using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using MySql.Data.MySqlClient;

namespace Calendar
{
    class CalendarORM
    {
        MySqlConnection con = new MySqlConnection("server=localhost;database=smartPM;uid=root;"); //burhan_smartmanager   password=abcdaaa


        public GlobalInfo.loginStatus Login(string userId, string psw)
        {
            con.Open();
            string qry = "select password,userrole from user where loginname= '" + userId  + "' ";
            MySqlCommand cmd = new MySqlCommand(qry);
            cmd.Connection = con;
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            con.Close();

            GlobalInfo.UserRole = Convert.ToInt32(dt.Rows[0][1].ToString());
            //GlobalInfo.eUserRolle=
            if (dt.Rows.Count  == 0)
                return GlobalInfo.loginStatus.UserNotExist;
            else if ((dt.Rows.Count  == 1) && dt.Rows[0][0].ToString().Equals(psw) == false)
                return GlobalInfo.loginStatus.InvalidPassword ;
            else if ((dt.Rows.Count  == 1) && dt.Rows[0][0].ToString().Equals(psw) == true)
                return GlobalInfo.loginStatus.SuccessfulLogin;
            else return GlobalInfo.loginStatus.UserNotExist;

        }


        public DataTable  getDelayedTasks()
        {
            con.Open();
            string qry = "select * from vwtask2 where (assignedTo='" + GlobalInfo.UserId + "'  or ProjectManager='" + GlobalInfo.UserId + "' ) and actualEndDate ='1970-01-01 00:00:00' and estimatedEndDate <'" + DateTime.Today.ToString("yyyy-MM-dd") + "' ";
            MySqlCommand cmd = new MySqlCommand(qry);
            cmd.Connection = con;
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            con.Close();

            return dt;
        }

        public DataTable getMilestonesWithBudgetOverConsumed()
        {
            con.Open();
            string qry = "call calcMilestonesCosts() ";
            MySqlCommand cmd = new MySqlCommand(qry);
            cmd.Connection = con;
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            qry = "select * from vwMilestoneCosts where consumedCost > estimatedCost "; //tempMilestoneCosts
            cmd = new MySqlCommand(qry);
            cmd.Connection = con;
            da = new MySqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            con.Close();

            return dt;
        }


        public DataTable getConflictingMilestones()
        {
            con.Open();
            string qry = "select * from vwTaskDep2 where actualenddate='1970-01-01 00:00:00' and mId1 in (select milestoneid from milestone where estimatedEndDate < CURRENT_DATE() and ( EndDate is null or enddate='1970-01-01 00:00:00') )";
            MySqlCommand cmd = new MySqlCommand(qry);
            cmd.Connection = con;
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            con.Close();

            return dt;
        }
    }
}
